﻿using System.Data;

namespace WorkOrder.Business
{
    public class AreaReport
    {
        DbHelper _dbhelper = new DbHelper();

        #region 返回区域资料
        public DataSet GetArea()
        {
            string sqlstr = "select area_id,area_name from Table_Area";
            return _dbhelper.Query(sqlstr);
        }
        #endregion

        #region 开通客户（家）
        public DataTable GetNewCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=1" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 开通已完成
        public DataTable GetNewCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=1 and (A.order_status='已关闭' or A.order_status='待业务确认')" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 开通支撑次数
        public DataTable GetNewCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id and C.order_type_id=1" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消客户（家）
        public DataTable GetCancelCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=2" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消已完成
        public DataTable GetCancelCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=2 and (A.order_status='已关闭' or A.order_status='待业务确认')" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消支撑次数
        public DataTable GetCancelCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id and C.order_type_id=2" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整客户（家）
        public DataTable GetAdjustCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=3" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整已完成
        public DataTable GetAdjustCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=3 and (A.order_status='已关闭' or A.order_status='待业务确认')" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整支撑次数
        public DataTable GetAdjustCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id and C.order_type_id=3" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 新增退单
        public DataTable GetNewReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=1 and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 取消退单
        public DataTable GetCancelReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=2 and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 调整退单
        public DataTable GetAdjustReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.order_type_id=3 and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 总计客户
        public DataTable GetTotalCustomers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 总计支撑次数
        public DataTable GetTotalCustomersNumbers(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_record_id as varchar(40))) as number from Table_Order_record A,Table_Product B,table_work_order C where A.order_id=C.order_id and C.product_id=B.product_id" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 总计完成客户
        public DataTable GetTotalCustomersIsFinished(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and (A.order_status='已关闭' or A.order_status='待业务确认')" + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion

        #region 总计退单
        public DataTable GetTotalReturnBill(string str)
        {
            string sqlstr = "select B.product_name,count(cast(A.order_id as varchar(40))) as number from table_work_order A,Table_Product B where A.product_id=B.product_id and A.is_back<>0 and A.finish_time between " + str + " group by B.product_name";
            return _dbhelper.Query("", sqlstr);
        }
        #endregion
    }
}
